C:/Projekte/RAIS/dataaccesslayer/RADEVSearch.cs

Go to the documentation of this file.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using RAIS.Common.RADEVSearchClasses;
namespace RAIS.DataAccessLayer
{
    public static class RADEVSearch
    {
        public static Dictionary<int, string> GetValueList(string functionName)
        {
            return GetValueListFromQuery("SELECT * FROM " + functionName);
        }

        public static Dictionary<int, string> GetValueListFromQuery(string queryString)
        {
            Dictionary<int, string> result = new Dictionary<int, string>();
            using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(queryString, sqlConnection))
                {
                    using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                    {
                        while (sqlReader.Read())
                        {
                            string name = sqlReader["Name"] is DBNull ? "" : sqlReader["Name"].ToString();
                            result.Add((int)sqlReader["ID"], name);
                        }
                    }
                }
            }
            return result;
        }

        public static List<RADEVRow> GetRadevSearchResult(string searchSQLString)
        {
            Dictionary<string, RADEVRow> retList = new Dictionary<string, RADEVRow>();

            using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(searchSQLString, sqlConnection))
                {
                    using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                    {
                        while (sqlReader.Read())
                        {
                            string IAEAEventID = sqlReader["IAEAEventID"] is DBNull ? "" : sqlReader["IAEAEventID"].ToString();
                            string Fasilityname = sqlReader["Fasilityname"] is DBNull ? "" : sqlReader["Fasilityname"].ToString();
                            string EventDate = sqlReader["EventDate"] is DBNull ? "" : sqlReader["EventDate"].ToString();
                            string Radiationsource = sqlReader["Radiationsource"] is DBNull ? "" : sqlReader["Radiationsource"].ToString();
                            string SpecificPractice = sqlReader["SpecificPractice"] is DBNull ? "" : sqlReader["SpecificPractice"].ToString();
                            string ReportStatus = sqlReader["ReportStatus"] is DBNull ? "" : sqlReader["ReportStatus"].ToString();

                            if (!retList.ContainsKey(IAEAEventID))
                                retList.Add(IAEAEventID, new RADEVRow((int)sqlReader["ID"], IAEAEventID, Fasilityname, EventDate, Radiationsource, 
                                                        SpecificPractice, ReportStatus));
                            else
                                retList[IAEAEventID].AddRADEV(SpecificPractice, ReportStatus);
                        }
                    }
                }
            }
            List<RADEVRow> list = new List<RADEVRow>();
            foreach (RADEVRow row in retList.Values)
                list.Add(row);
            return list;
        }

        public static void SaveSearchCriterias(string name, string searchString)
        {
            using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(string.Format("INSERT INTO RADEVSearchCriterias([Name],[QueryString]) VALUES('{0}','{1}')", name, searchString.Replace("'","''")), sqlConnection))
                {
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

        public static string GetSavedSearch(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(string.Format("SELECT QueryString FROM RADEVSearchCriterias WHERE [ID]={0}", id), sqlConnection))
                {
                    return (string)sqlCommand.ExecuteScalar();
                }
            }
        }
    }
}